<?php

class m_aktifitas extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    // get last inserted id
    function get_last_inserted_id() {
        return $this->db->insert_id();
    }

    // get total dosen
    function get_total_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM
                (
                    SELECT a.* FROM users a 
                    JOIN com_role_user b ON a.user_id = b.user_id 
                    JOIN com_role c ON b.role_id = c.role_id 
                    LEFT JOIN kelas d ON a.user_id = d.dosen_kd
                    WHERE c.role_id = 4 AND d.tahun = ?  
                    AND d.semester = ? AND a.nama_lengkap LIKE ?  
                    GROUP BY a.user_id
                )r";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get data dosen
    function get_data_dosen($params) {
        $sql = "SELECT a.*, d.tahun, d.semester FROM users a 
                JOIN com_role_user b ON a.user_id = b.user_id 
                JOIN com_role c ON b.role_id = c.role_id 
                LEFT JOIN kelas d ON a.user_id = d.dosen_kd
                WHERE c.role_id = 4 AND d.tahun = ?  
                AND d.semester = ? AND a.nama_lengkap LIKE ? 
                GROUP BY a.user_id 
                ORDER BY a.nama_lengkap ASC 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total makul per dosen
    function get_total_makul_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                WHERE a.tahun = ? AND a.semester = ?  
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get total kelas per dosen
    function get_total_kelas_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM kelas 
                WHERE tahun = ? AND semester = ?  
                AND dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get makul diampu
    function get_makul_diampu($params) {
        $sql = "SELECT b.*, d.makul_nm FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN users c ON b.dosen_kd = c.user_id 
                JOIN mata_kuliah d ON b.makul_kd = d.makul_kd 
                WHERE a.tahun = ? AND a.semester = ?  
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data aktifitas dosen
    function get_data_aktifitas_dosen($params) {
        $sql = "SELECT a.*, b.makul_nm, b.makul_sks FROM kelas a 
                JOIN mata_kuliah b ON a.makul_kd = b.makul_kd 
                WHERE a.tahun = ? AND a.semester = ?  
                AND a.dosen_kd = ? AND a.makul_kd LIKE ?  
                ORDER BY b.makul_nm ASC, a.kelas_nm ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total rencana pertemuan
    function get_total_rencana_pertemuan($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM silabus a 
                JOIN silabus_detail b ON a.silabus_kd = b.silabus_kd 
                WHERE a.tahun = ? AND a.semester = ? 
                AND a.dosen_kd = ? AND a.makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get total realisasi pertemuan
    function get_total_realisasi_pertemuan($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM kelas a 
                JOIN kelas_detail b ON a.kelas_kd = b.kelas_kd 
                WHERE a.tahun = ? AND a.semester = ? 
                AND a.dosen_kd = ? AND a.kelas_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

}